Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Parallel Query Operation

With traditional queries such as table scans, the server process reads the data sequentially (see Figure 10.8). Much of the time spent in this query is spent waiting for I/Os to complete.


Figure 10.8  A table scan without parallel query.

A parallel query splits the query into several different pieces, each one processed by a different server process. These processes are called query servers. The query servers are dispatched by a process known as the query coordinator.

The query coordinator dispatches the query servers and coordinates the results from all the servers to send back to the user. The result of this arrangement is that many smaller table scans take place under the hood (transparent to the user). From the user’s standpoint, it is simply a much faster table scan. Figure 10.9 shows a parallel query.


Figure 10.9  A table scan with parallel query.

The query coordinator is given an SQL statement and a degree of parallelism and is responsible for dividing the query among the query servers and integrating the individual results into one result. The degree of parallelism is the number of query servers assigned to the particular query.

The Oracle server can make parallel the following operations:

  Joins
  Sorts
  Table scans

Each of these operations have requirements that determine how the query is parallelized. The performance achieved by the parallel query is determined both by the size of the data to be accessed and the degree of parallelism achieved.

How the query is parallelized (if at all) is determined by the query coordinator. The decision is made in this order:

1.  The optimizer determines the execution plan of the statement.
2.  The query coordinator determines which operations can be performed in parallel.
3.  The query coordinator determines how many query servers to enlist.
4.  The query coordinator enlists query servers that perform the query.
5.  The query coordinator reassembles the resulting data and passes it back to the user.

The degree of parallelism is determined using the following precedence:

1.  Query Hints. User-defined hints included in the SQL statement have the highest precedence.
2.  Table Definition. The default degree of parallelism defined for the table has second precedence.
3.  Initialization Parameters. Finally, the Oracle initialization parameters are used.

Regardless of what these values are set to, the number of query servers cannot exceed the number of query servers available in the query server pool. This number is specified by the Oracle initialization parameter, PARALLEL_MAX_SERVERS.

Hints for the degree of parallelism are set within a comment string in the SQL statement. The syntax of this comment is as follows:

PARALLEL ( alias_or_tablename , [ integer/DEFAULT ] [ , integer/DEFAULT ] )

The PARALLEL hint specifies the table or alias being scanned, followed by a value for the number of query servers to be used (or the DEFAULT). The final optional value specifies how the table is to be split among different instances of a parallel server. These hints are described in detail in Chapter 30, “Using Hints.” Here is an example using the DOGS table introduced earlier in this chapter:

SELECT /*+ FULL(dogs) PARALLEL(dogs, 4) */
dogname
FROM dogs;

When you add the FULL and PARALLEL hints to this statement, the Oracle optimizer creates an execution plan that uses a full-table scan. Furthermore, this tablescan is executed with a parallel degree of 4 if the query servers are available. This statement overrides both the degree of parallelism specified in the table definition and the default Oracle initialization parameters.

The hint NOPARALLEL disables parallel scanning of a table and overrides the specified degree of parallelism. The NOPARALLEL hint has the following syntax:

NOPARALLEL ( alias_or_tablename )

Parallel Query Tuning

Parallel query operations can be very effective on multiprocessor or parallel-processing computers; they can also be effective on uniprocessor systems where much of the time is spent waiting for I/O operations to complete. Systems with sufficient I/O bandwidth—and especially systems with disk arrays—benefit from parallel query operations.

If your system is typically processing at 100 percent of your CPU utilization and you have a small number of disk drives, you will probably not benefit from parallel query operations. If your system is extremely memory limited, you also will probably not benefit from parallel query operations.

The two areas that can be tuned for parallel queries are I/O and parallel servers. By properly configuring your data files, you can help parallel queries be more effective.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.